<?php

namespace common\models\data;

use Yii;
use yii\behaviors\TimestampBehavior;
use yii\helpers\ArrayHelper;
use yii\helpers\FileHelper;
use common\models\rp\RedPacket;
use common\models\rp\RedPacketLog;
use common\models\match\Match;
use common\models\match\Prize;
use common\models\match\MatchRewardLog;
use common\models\team\TeamAccountLog;
use common\models\user\UserPay;
use common\models\user\UserWithDraw;
use backend\models\Admin;
use backend\models\user\UserBaseInfor;
use backend\models\user\UserRealInfo;
use api\models\mysql\user\UserGiftLogs;
use PHPExcel;
use PHPExcel_IOFactory;
use Exception;

require_once Yii::getAlias('@spider.sdk');

/**
 * This is the model class for table "{{data_report_fee_log}}".
 *
 * @property int $id 自增ID
 * @property string $fee_1
 * @property string $fee_2
 * @property string $fee_3
<<<<<<< .working
 * @property string $type 类型 extract_fee,match_fee,send_fee,red_packet_fee,pay_fee,gift_fee
=======
 * @property string $type 类型 match_fee,send_fee,red_packet_fee,pay_fee,gift_fee,team_with_draw_fee,team_with_draw_invoice_fee
>>>>>>> .merge-right.r58627
 * @property string $time_type 时间类型 year,half,quater,month,week,day
 * @property string $file_url 文件地址
 * @property int $start_at
 * @property int $end_at
 * @property int $created_by
 * @property int $created_at
 * @property int $updated_by
 * @property int $updated_at
 */
class DataReportFeeLog extends \yii\db\ActiveRecord
{
    public $_type = [
        ['key' => 'bonus_with_draw_fee', 'value' => '奖金提现'],
        ['key' => 'bonus_with_draw_fee', 'value' => '奖金提现'],
        ['key' => 'match_fee', 'value' => '赛事奖金'],
        ['key' => 'send_fee', 'value' => '发放奖金'],
        ['key' => 'red_packet_fee', 'value' => '红包发送'],
        ['key' => 'red_packet_with_draw_fee', 'value' => '红包提现'],
        ['key' => 'pay_fee', 'value' => '充值购买'],
        ['key' => 'gift_fee', 'value' => '道具消费'],
        ['key' => 'pay_fee', 'value' => '充值购买'],
        ['key' => 'team_with_draw_fee', 'value' => '团队提现'],
        ['key' => 'team_with_draw_invoice_fee', 'value' => '团队提现_发票'],
    ];

    public $_time_type = [
        ['key' => 'year', 'value' => '年'],
        ['key' => 'half', 'value' => '半年'],
        ['key' => 'quater', 'value' => '季度'],
        ['key' => 'month', 'value' => '月'],
        ['key' => 'week', 'value' => '周'],
        ['key' => 'day', 'value' => '日'],
    ];

    public $_start_at;
    public $_end_at;
    public $_time_type_name;
    public $_type_name;

    private $_page = 0;
    protected $_pageSize = 1000;

    /**
     * {@inheritdoc}
     */
    public static function tableName()
    {
        return '{{data_report_fee_log}}';
    }

    /**
     * @return \yii\db\Connection the database connection used by this AR class.
     */
    public static function getDb()
    {
        return Yii::$app->get('db');
    }


    public function behaviors()
    {
        return [
            [
                'class' => TimestampBehavior::class,
            ],
        ];
    }

    /**
     * {@inheritdoc}
     */
    public function rules()
    {
        return [
            [['fee_1', 'fee_2', 'fee_3'], 'number'],
            [['type', 'time_type', 'start_at', 'end_at'], 'required'],
            [['start_at', 'end_at', 'created_by', 'created_at', 'updated_by', 'updated_at'], 'integer'],
            [['type', 'time_type'], 'string', 'max' => 50],
            [['file_url'], 'string', 'max' => 255],
        ];
    }

    /**
     * {@inheritdoc}
     */
    public function attributeLabels()
    {
        return [
            'id' => Yii::t('app', 'ID'),
            'type' => Yii::t('app', '类型'),
            'time_type' => Yii::t('app', '时间类型'),
            'file_url' => Yii::t('app', '文件地址'),
            'start_at' => Yii::t('app', '开始时间'),
            'end_at' => Yii::t('app', '结束时间'),
            'created_by' => Yii::t('app', 'Created By'),
            'created_at' => Yii::t('app', 'Created At'),
            'updated_by' => Yii::t('app', 'Updated By'),
            'updated_at' => Yii::t('app', 'Updated At'),
        ];
    }

    /**
     * 奖金提现
     * @param $time_type
     * @param $start_at
     * @param $end_at
     */
    public function bonusWithDrawFee($time_type, $start_at, $end_at)
    {
        if (!in_array($time_type, ArrayHelper::getColumn($this->_time_type, 'key'))) {
            return false;
        }

        $this->_start_at = $start_at;
        $this->_end_at = $end_at;
        $this->_type_name = ArrayHelper::map($this->_type, 'key', 'value')['bonus_with_draw_fee'];
        $this->_time_type_name = ArrayHelper::map($this->_time_type, 'key', 'value')[$time_type];

        $query = UserWithDraw::find();
        $query->andWhere(['status' => 2]);
        $query->andWhere(['type' => 2]);
        $query->andWhere(['between', 'updated_at', strtotime($start_at), strtotime($end_at)]);
//        $sql = $query->createCommand()->rawSql;
        $count = $query->count('id');

        if (empty($count)) {
            $params = [
                'start_at' => strtotime($start_at),
                'end_at' => strtotime($end_at),
                'time_type' => $time_type,
                'type' => 'bonus_with_draw_fee',
                'created_by' => 1,
            ];

            return $this->saveModel($params);
        }

        //申请提现金额(税前)
        $newQuery = clone $query;
        $fee_1 = $newQuery->sum('fee');
        //申请提现金额(税后)
        $newQuery = clone $query;
        $fee_2 = $newQuery->sum('currency_fee_total');

        $offset = ceil($count / $this->_pageSize);

        $data = [];
        for ($i = $this->_page; $i <= $offset; $i++) {
            $list = $query->select(['user_guid', 'biz_order_id', 'fee', 'status', 'currency_fee_total', 'created_at', 'updated_at'])
                ->orderBy('updated_at')->offset($i * $this->_pageSize)->limit($this->_pageSize)->createCommand()
                ->queryAll();
//            ->rawSql;

            $userIDs = array_unique(ArrayHelper::getColumn($list, 'user_guid'));
            $userbase = UserBaseInfor::find()->select(['user_guid', 'user_tel'])->where(['user_guid' => $userIDs])->all();
            $user_tel = ArrayHelper::map($userbase, 'user_guid', 'user_tel');
            $userreal = UserRealInfo::find()->select(['user_guid', 'identity', 'real_name'])->where(['user_guid' => $userIDs])->all();
            $user_real_name = ArrayHelper::map($userreal, 'user_guid', 'real_name');
            $user_identity = ArrayHelper::map($userreal, 'user_guid', 'identity');

            foreach ($list as &$key) {
                $key['fee'] = round($key['fee'] * 0.01, 2);
                $key['currency_fee_total'] = round($key['currency_fee_total'] * 0.01, 2);
                $key['created_at'] = date('Y-m-d H:i:s', $key['created_at']);
                $key['updated_at'] = date('Y-m-d H:i:s', $key['updated_at']);
                $key['user_tel'] = !empty($user_tel[$key['user_guid']]) ? $user_tel[$key['user_guid']] : '';
                $key['user_real_name'] = !empty($user_real_name[$key['user_guid']]) ? $user_real_name[$key['user_guid']] : '';
                $key['user_identity'] = !empty($user_identity[$key['user_guid']]) ? $user_identity[$key['user_guid']] : '';
                switch ($key['status']) {
                    case 1:
                        $key['status'] = '等待提现';
                        break;
                    case 2:
                        $key['status'] = '提现成功';
                        break;
                    case 3:
                        $key['status'] = '提现失败';
                        break;
                }
            }
            unset($key);

            $data = array_merge($data, $list);
        }

<<<<<<< .working
        $fee_1 = array_sum(ArrayHelper::getColumn($data, 'fee'));
        $fee_1 = $fee_1 * 100;
        $fee_2 = array_sum(ArrayHelper::getColumn($data, 'pay_fee'));
        $fee_2 = $fee_2 * 100;

=======
>>>>>>> .merge-right.r58627
        $table = [
            ['biz_order_id', '订单号'],
            ['user_guid', '用户ID'],
            ['user_tel', '手机号'],
            ['user_real_name', '真实姓名'],
            ['user_identity', '身份证号'],
            ['fee', '申请提现金额（税前）'],
            ['created_at', '申请提现时间'],
            ['currency_fee_total', '实际提现金额（税后）'],
            ['updated_at', '到账时间'],
            ['status', '订单状态'],
        ];

        $filePath = $this->filePath($table, $data);

        if (empty($filePath)) {
            return false;
        }

        $params = [
            'fee_1' => $fee_1,
            'fee_2' => $fee_2,
            'start_at' => strtotime($start_at),
            'end_at' => strtotime($end_at),
            'time_type' => $time_type,
            'type' => 'bonus_with_draw_fee',
            'created_by' => 1,
            'file_url' => $filePath
        ];

        return $this->saveModel($params);
    }

    /**
     * 奖金提现
     * @param $time_type
     * @param $start_at
     * @param $end_at
     */
    public function bonusWithDrawFee($time_type, $start_at, $end_at)
    {
        if (!in_array($time_type, ArrayHelper::getColumn($this->_time_type, 'key'))) {
            return false;
        }

        $this->_start_at = $start_at;
        $this->_end_at = $end_at;
        $this->_type_name = ArrayHelper::map($this->_type, 'key', 'value')['bonus_with_draw_fee'];
        $this->_time_type_name = ArrayHelper::map($this->_time_type, 'key', 'value')[$time_type];

        $query = UserWithDraw::find();
        $query->andWhere(['status' => 2]);
        $query->andWhere(['type' => 2]);
        $query->andWhere(['between', 'updated_at', strtotime($start_at), strtotime($end_at)]);
//        $sql = $query->createCommand()->rawSql;
        $count = $query->count('id');

        if (empty($count)) {
            $params = [
                'start_at' => strtotime($start_at),
                'end_at' => strtotime($end_at),
                'time_type' => $time_type,
                'type' => 'bonus_with_draw_fee',
                'created_by' => 1,
            ];

            return $this->saveModel($params);
        }

        //申请提现金额(税前)
        $newQuery = clone $query;
        $fee_1 = $newQuery->sum('fee');
        //申请提现金额(税后)
        $newQuery = clone $query;
        $fee_2 = $newQuery->sum('currency_fee_total');

        $offset = ceil($count / $this->_pageSize);

        $data = [];
        for ($i = $this->_page; $i <= $offset; $i++) {
            $list = $query->select(['user_guid', 'order_id', 'fee', 'status', 'currency_fee_total', 'created_at', 'updated_at'])
                ->orderBy('updated_at')->offset($i * $this->_pageSize)->limit($this->_pageSize)->createCommand()
                ->queryAll();
//            ->rawSql;

            $userIDs = array_unique(ArrayHelper::getColumn($list, 'user_guid'));
            $userbase = UserBaseInfor::find()->select(['user_guid', 'user_tel'])->where(['user_guid' => $userIDs])->all();
            $user_tel = ArrayHelper::map($userbase, 'user_guid', 'user_tel');
            $userreal = UserRealInfo::find()->select(['user_guid', 'identity', 'real_name'])->where(['user_guid' => $userIDs])->all();
            $user_real_name = ArrayHelper::map($userreal, 'user_guid', 'real_name');
            $user_identity = ArrayHelper::map($userreal, 'user_guid', 'identity');

            foreach ($list as &$key) {
                $key['fee'] = round($key['fee'] * 0.01, 2);
                $key['currency_fee_total'] = round($key['currency_fee_total'] * 0.01, 2);
                $key['created_at'] = date('Y-m-d H:i:s', $key['created_at']);
                $key['updated_at'] = date('Y-m-d H:i:s', $key['updated_at']);
                $key['user_tel'] = !empty($user_tel[$key['user_guid']]) ? $user_tel[$key['user_guid']] : '';
                $key['user_real_name'] = !empty($user_real_name[$key['user_guid']]) ? $user_real_name[$key['user_guid']] : '';
                $key['user_identity'] = !empty($user_identity[$key['user_guid']]) ? $user_identity[$key['user_guid']] : '';
                switch ($key['status']) {
                    case 1:
                        $key['status'] = '等待提现';
                        break;
                    case 2:
                        $key['status'] = '提现成功';
                        break;
                    case 3:
                        $key['status'] = '提现失败';
                        break;
                }
            }
            unset($key);

            $data = array_merge($data, $list);
        }

        $table = [
            ['order_id', '订单号'],
            ['user_guid', '用户ID'],
            ['user_tel', '手机号'],
            ['user_real_name', '真实姓名'],
            ['user_identity', '身份证号'],
            ['fee', '申请提现金额（税前）'],
            ['created_at', '申请提现时间'],
            ['currency_fee_total', '实际提现金额（税后）'],
            ['updated_at', '到账时间'],
            ['status', '订单状态'],
        ];

        $filePath = $this->filePath($table, $data);

        if (empty($filePath)) {
            return false;
        }

        $params = [
            'fee_1' => $fee_1,
            'fee_2' => $fee_2,
            'start_at' => strtotime($start_at),
            'end_at' => strtotime($end_at),
            'time_type' => $time_type,
            'type' => 'bonus_with_draw_fee',
            'created_by' => 1,
            'file_url' => $filePath
        ];

        return $this->saveModel($params);
    }

    /**
     * 获取赛事奖金
     * @param $time_type
     * @param $start_at
     * @param $end_at
     * @return bool|\yii\db\DataReader
     * @throws \yii\db\Exception
     */
    public function matchFee($time_type, $start_at, $end_at)
    {
        if (!in_array($time_type, ArrayHelper::getColumn($this->_time_type, 'key'))) {
            return false;
        }
        $this->_start_at = $start_at;
        $this->_end_at = $end_at;
        $this->_type_name = ArrayHelper::map($this->_type, 'key', 'value')['match_fee'];
        $this->_time_type_name = ArrayHelper::map($this->_time_type, 'key', 'value')[$time_type];

        $query = Match::find();
        $query->leftJoin('match_account', 'match_account.match_id=match.id');
        $query->andWhere(['match.status' => 2]);
        $query->andWhere(['between', 'match_account.updated', $start_at, $end_at]);
//        $query->andWhere(['OR',
//            ['>', 'match_account.amount_total', 0],
//            ['>', 'match_account.amount_balance', 0]
//        ]);
        $count = $query->count('match.id');

        if (empty($count)) {
            $params = [
                'start_at' => strtotime($start_at),
                'end_at' => strtotime($end_at),
                'time_type' => $time_type,
                'type' => 'match_fee',
                'created_by' => 1,
            ];

            return $this->saveModel($params);
        }
        // 奖金总额
        $fee_1 = $query->sum('match_account.amount_total');
        $fee_1 = $fee_1 * 100;
        // 奖金余额
        $fee_2 = $query->sum('match_account.amount_balance');
        $fee_2 = $fee_2 * 100;

        $offset = ceil($count / $this->_pageSize);

        $data = [];
        for ($i = $this->_page; $i <= $offset; $i++) {
            $list = $query->select([
                'match.id', 'match.name', 'match.start_time', 'match.status',
                'match_account.amount_total as fee_total', 'match_account.amount_balance as fee_balance',
                'match_account.amount_sponsor_current as fee_sponsor_current', 'match_account.amount_share_current as fee_share_current',
            ])->orderBy('match.id')->offset($i * $this->_pageSize)->limit($this->_pageSize)->createCommand()
                ->queryAll();
//            ->rawSql;

            $matchIDs = ArrayHelper::getColumn($list, 'id');

            $prize = Prize::find()->select(['match_id', 'sum(worth) as worth'])->where(['match_id' => $matchIDs])->groupBy('match_id')->all();
            $prizeMap = ArrayHelper::map($prize, 'match_id', 'worth');
            foreach ($list as &$key) {
                $key['fee_total'] = floatval($key['fee_total']);
                $key['fee_balance'] = floatval($key['fee_balance']);
                $key['fee_sponsor_current'] = floatval($key['fee_sponsor_current']);
                $key['fee_share_current'] = floatval($key['fee_share_current']);
                $key['worth'] = floatval($prizeMap[$key['id']]);
            }
            unset($key);

            $data = array_merge($data, $list);
        }

        $table = [
            ['id', '赛事ID'],
            ['name', '赛事名称'],
            ['start_time', '赛事开始时间'],
            ['status', '比赛状态'],
            ['worth', '赛事奖励总价值'],
            ['fee_total', '奖金总价值'],
            ['fee_sponsor_current', '浮动'],
            ['fee_balance', '奖金余额'],
            ['fee_share_current', '宣传'],
        ];

        $filePath = $this->filePath($table, $data);

        if (empty($filePath)) {
            return false;
        }

        $params = [
            'fee_1' => $fee_1,
            'fee_2' => $fee_2,
            'start_at' => strtotime($start_at),
            'end_at' => strtotime($end_at),
            'time_type' => $time_type,
            'type' => 'match_fee',
            'created_by' => 1,
            'file_url' => $filePath
        ];

        return $this->saveModel($params);
    }


    /**
     * 获取赛事发放
     * @param $time_type
     * @param $start_at
     * @param $end_at
     * @return bool
     * @throws \yii\db\Exception
     */
    public function sendFee($time_type, $start_at, $end_at)
    {
        if (!in_array($time_type, ArrayHelper::getColumn($this->_time_type, 'key'))) {
            return false;
        }

        $this->_start_at = $start_at;
        $this->_end_at = $end_at;
        $this->_type_name = ArrayHelper::map($this->_type, 'key', 'value')['send_fee'];
        $this->_time_type_name = ArrayHelper::map($this->_time_type, 'key', 'value')[$time_type];

        $query = MatchRewardLog::find();

        $query->andWhere(['status' => 1]);
        $query->andWhere(['between', 'updated', $start_at, $end_at]);
        $query->andWhere(['>', 'amount', 0]);

        $count = $query->count('id');
        if (empty($count)) {
            $params = [
                'start_at' => strtotime($start_at),
                'end_at' => strtotime($end_at),
                'time_type' => $time_type,
                'type' => 'send_fee',
                'created_by' => 1,
            ];

            return $this->saveModel($params);
        }

        //发放总额
        $fee_1 = $query->sum('amount');
        $fee_1 = $fee_1 * 100;

        $offset = ceil($count / $this->_pageSize);

        $data = [];
        for ($i = $this->_page; $i <= $offset; $i++) {
            $list = $query->select([
                'user_id', 'role', 'match_id', 'amount as fee', 'created', 'status', 'remark'
            ])->orderBy('match_id')->offset($i * $this->_pageSize)->limit($this->_pageSize)->createCommand()->queryAll();
            foreach ($list as &$key) {
                $key['fee'] = floatval($key['fee']);
            }
            unset($key);

            $data = array_merge($data, $list);
        }

        $table = [
            ['user_id', 'UID'],
            ['role', '角色'],
            ['match_id', '赛事ID'],
            ['fee', '金额'],
            ['created', '时间'],
            ['status', '状态'],
            ['remark', '原因'],
        ];

        $filePath = $this->filePath($table, $data);

        if (empty($filePath)) {
            return false;
        }

        $params = [
            'fee_1' => $fee_1,
            'start_at' => strtotime($start_at),
            'end_at' => strtotime($end_at),
            'time_type' => $time_type,
            'type' => 'send_fee',
            'created_by' => 1,
            'file_url' => $filePath
        ];

        return $this->saveModel($params);
    }

    /**
     * 红包发送
     * @param $time_type
     * @param $start_at
     * @param $end_at
     */
    public function redPacketFee($time_type, $start_at, $end_at)
    {
        if (!in_array($time_type, ArrayHelper::getColumn($this->_time_type, 'key'))) {
            return false;
        }

        $this->_start_at = $start_at;
        $this->_end_at = $end_at;
        $this->_type_name = ArrayHelper::map($this->_type, 'key', 'value')['red_packet_fee'];
        $this->_time_type_name = ArrayHelper::map($this->_time_type, 'key', 'value')[$time_type];

        $query = RedPacketLog::find();
        $query->andWhere(['not', ['user_id' => '']]);
        $query->andWhere(['between', 'updated', $start_at, $end_at]);
//        $sql = $query->createCommand()->rawSql;
        $count = $query->count('id');

        if (empty($count)) {
            $params = [
                'start_at' => strtotime($start_at),
                'end_at' => strtotime($end_at),
                'time_type' => $time_type,
                'type' => 'red_packet_fee',
                'created_by' => 1,
            ];

            return $this->saveModel($params);
        }

        //发放总额
//        $newQuery = clone $query;
//        $fee_1 = $newQuery->sum('amount');
//        $fee_1 = $fee_1 * 100;
        //领取总额
        $newQuery = clone $query;
        $fee_2 = $newQuery->andWhere(['type' => 0])->sum('amount');
        $fee_2 = $fee_2 * 100;
        $newQuery = clone $query;
        //退还总额
        $fee_3 = $newQuery->andWhere(['type' => 1])->sum('amount');
        $fee_3 = $fee_3 * 100;

        $offset = ceil($count / $this->_pageSize);

        $data = [];
        for ($i = $this->_page; $i <= $offset; $i++) {
            $list = $query->select(['rp_id', 'match_id', 'user_id as get_user_id', 'amount as get_fee', 'type', 'created', 'receive_time'])
                ->orderBy('id')->offset($i * $this->_pageSize)->limit($this->_pageSize)->createCommand()
                ->queryAll();
//            ->rawSql;
            $redPacketIds = array_unique(ArrayHelper::getColumn($list, 'rp_id'));
            $redPacket = RedPacket::find()->select(['id', 'user_id', 'amount_total'])->andWhere(['id' => $redPacketIds])->all();
            $sendRedPacketUser = ArrayHelper::map($redPacket, 'id', 'user_id');
            $sendRedPacketFee = ArrayHelper::map($redPacket, 'id', 'amount_total');

            $matchIds = array_unique(ArrayHelper::getColumn($list, 'match_id'));
            $match = Match::find()->select(['id', 'name'])->andWhere(['id' => $matchIds])->all();
            $matchName = ArrayHelper::map($match, 'id', 'name');

            foreach ($list as &$key) {
                if (!empty($sendRedPacketFee[$key['rp_id']])) {
                    $key['send_fee'] = floatval($sendRedPacketFee[$key['rp_id']]);
                }
                if (!empty($sendRedPacketUser[$key['rp_id']])) {
                    $key['send_user_id'] = $sendRedPacketUser[$key['rp_id']];
                }
                if (!empty($matchName[$key['match_id']])) {
                    $key['match_name'] = $matchName[$key['match_id']];
                }
                $key['red_packet_send_at'] = $key['created'];
                $key['red_packet_get_at'] = $key['receive_time'];
                if ($key['type'] == 1) {
                    $key['is_back'] = '是';
                    $key['back_at'] = $key['receive_time'];
                    $key['status'] = '已退款';
                    $key['return_fee'] = floatval($key['get_fee']);
                    $key['get_fee'] = 0;
                } else {
                    $key['is_back'] = '否';
                    $key['back_at'] = '';
                    $key['status'] = '已领取';
                    $key['get_fee'] = floatval($key['get_fee']);
                    $key['return_fee'] = 0;
                }
            }
            unset($key);

            $data = array_merge($data, $list);
        }
        $redPacketIds = array_unique(ArrayHelper::getColumn($data, 'rp_id'));
        foreach ($redPacketIds as $key) {
            $red[$key] = 0;
        }

        foreach ($data as &$key) {
            if ($red[$key['rp_id']] == 0) {
                $red[$key['rp_id']] = 1;
            } else {
                $key['send_fee'] = 0;
            }
        }
        unset($key);

        //发放总额
        $fee_1 = array_sum(ArrayHelper::getColumn($data, 'send_fee'));
        $fee_1 = $fee_1 * 100;

        $table = [
            ['red_packet_send_at', '红包发送时间'],
            ['red_packet_get_at', '红包领取时间'],
            ['match_id', '赛事ID'],
            ['match_name', '赛事名称'],
            ['rp_id', '红包ID'],
            ['send_user_id', '发送人ID'],
            ['send_fee', '发送金额'],
            ['get_user_id', '领取人ID'],
            ['get_fee', '领取金额'],
            ['is_back', '是否退款'],
            ['return_fee', '退款金额'],
            ['back_at', '退款时间'],
            ['status', '状态'],
        ];

        $filePath = $this->filePath($table, $data);

        if (empty($filePath)) {
            return false;
        }

        $params = [
            'fee_1' => $fee_1,
            'fee_2' => $fee_2,
            'fee_3' => $fee_3,
            'start_at' => strtotime($start_at),
            'end_at' => strtotime($end_at),
            'time_type' => $time_type,
            'type' => 'red_packet_fee',
            'created_by' => 1,
            'file_url' => $filePath
        ];

        return $this->saveModel($params);
    }

    /**
     * 红包提现
     * @param $time_type
     * @param $start_at
     * @param $end_at
     */
    public function redPacketWithDrawFee($time_type, $start_at, $end_at)
    {
        if (!in_array($time_type, ArrayHelper::getColumn($this->_time_type, 'key'))) {
            return false;
        }

        $this->_start_at = $start_at;
        $this->_end_at = $end_at;
        $this->_type_name = ArrayHelper::map($this->_type, 'key', 'value')['red_packet_with_draw_fee'];
        $this->_time_type_name = ArrayHelper::map($this->_time_type, 'key', 'value')[$time_type];

        $query = UserWithDraw::find();
        $query->andWhere(['status' => 2]);
        $query->andWhere(['type' => 3]);
        $query->andWhere(['between', 'updated_at', strtotime($start_at), strtotime($end_at)]);
//        $sql = $query->createCommand()->rawSql;
        $count = $query->count('id');

        if (empty($count)) {
            $params = [
                'start_at' => strtotime($start_at),
                'end_at' => strtotime($end_at),
                'time_type' => $time_type,
                'type' => 'red_packet_with_draw_fee',
                'created_by' => 1,
            ];

            return $this->saveModel($params);
        }

        //提现总额
        $newQuery = clone $query;
        $fee_1 = $newQuery->sum('fee');

        $offset = ceil($count / $this->_pageSize);

        $data = [];
        for ($i = $this->_page; $i <= $offset; $i++) {
            $list = $query->select(['user_guid', 'order_id', 'fee', 'created_at'])
                ->orderBy('updated_at')->offset($i * $this->_pageSize)->limit($this->_pageSize)->createCommand()
                ->queryAll();
//            ->rawSql;

            foreach ($list as &$key) {
                $key['fee'] = round($key['fee'] * 0.01, 2);
                $key['created_at'] = date('Y-m-d H:i:s', $key['created_at']);
            }
            unset($key);

            $data = array_merge($data, $list);
        }

        $table = [
            ['order_id', '订单ID'],
            ['user_guid', '用户ID'],
            ['created_at', '提现时间'],
            ['fee', '提现金额'],
        ];

        $filePath = $this->filePath($table, $data);

        if (empty($filePath)) {
            return false;
        }

        $params = [
            'fee_1' => $fee_1,
            'start_at' => strtotime($start_at),
            'end_at' => strtotime($end_at),
            'time_type' => $time_type,
            'type' => 'red_packet_with_draw_fee',
            'created_by' => 1,
            'file_url' => $filePath
        ];

        return $this->saveModel($params);
    }

    /**
     * 充值购买
     * @param $time_type
     * @param $start_at
     * @param $end_at
     */
    public function payFee($time_type, $start_at, $end_at)
    {

        if (!in_array($time_type, ArrayHelper::getColumn($this->_time_type, 'key'))) {
            return false;
        }

        $this->_start_at = $start_at;
        $this->_end_at = $end_at;
        $this->_type_name = ArrayHelper::map($this->_type, 'key', 'value')['pay_fee'];
        $this->_time_type_name = ArrayHelper::map($this->_time_type, 'key', 'value')[$time_type];

        $query = UserPay::find();
        $query->andWhere(['type' => 1]);
        //只要成功与失败的
        $query->andWhere(['status' => 2]);
        $query->andWhere(['between', 'updated_at', strtotime($start_at), strtotime($end_at)]);
//        $sql = $query->createCommand()->rawSql;
        $count = $query->count('id');

        if (empty($count)) {
            $params = [
                'start_at' => strtotime($start_at),
                'end_at' => strtotime($end_at),
                'time_type' => $time_type,
                'type' => 'pay_fee',
                'created_by' => 1,
            ];

            return $this->saveModel($params);
        }

        //充值总额
        $newQuery = clone $query;
        $fee_1 = $newQuery->sum('fee');
        //发放虚拟币总额
        $newQuery = clone $query;
        $fee_2 = $newQuery->sum('currency_fee_total');
        //渠道费总额 只有ios会有渠道费
        $newQuery = clone $query;
        $newPay = $newQuery->sum('pay_fee');
        $fee_3 = $fee_1 - $newPay;

        $offset = ceil($count / $this->_pageSize);

        $data = [];
        for ($i = $this->_page; $i <= $offset; $i++) {
            $list = $query->select(['order_id', 'user_guid', 'fee', 'pay_fee', 'currency_fee', 'extra_currency_fee', 'platform_os', 'created_at', 'status'])
                ->orderBy('id')->offset($i * $this->_pageSize)->limit($this->_pageSize)->createCommand()
                ->queryAll();
//            ->rawSql;

            foreach ($list as &$key) {
                $key['created_at'] = date('Y-m-d H:i:s', $key['created_at']);
                $key['platform_os'] = ($key['platform_os'] == 1) ? 'ios' : 'android';
                $key['platform_fee'] = floatval(round(($key['fee'] - $key['pay_fee']) * 0.01, 2));
                $key['fee'] = floatval(round($key['fee'] * 0.01, 2));
                $key['fee_1'] = floatval(round($key['pay_fee'] * 0.01, 2));
                $key['currency_fee'] = intval($key['currency_fee']);
                $key['extra_currency_fee'] = intval($key['extra_currency_fee']);
                $key['status'] = ($key['status'] == 2) ? '成功' : '失败';
            }
            unset($key);

            $data = array_merge($data, $list);
        }

        $table = [
            ['created_at', '充值时间'],
            ['order_id', '订单号'],
            ['user_guid', '用户ID'],
            ['currency_fee', '购币数量'],
            ['extra_currency_fee', '赠币数量'],
            ['fee', '充值金额'],
            ['fee_1', '实收金额'],
            ['platform_os', '渠道类型'],
            ['platform_fee', '渠道费'],
            ['status', '状态'],
        ];


        $filePath = $this->filePath($table, $data);

        if (empty($filePath)) {
            return false;
        }

        $params = [
            'fee_1' => $fee_1,
            'fee_2' => $fee_2,
            'fee_3' => $fee_3,
            'start_at' => strtotime($start_at),
            'end_at' => strtotime($end_at),
            'time_type' => $time_type,
            'type' => 'pay_fee',
            'created_by' => 1,
            'file_url' => $filePath
        ];

        return $this->saveModel($params);
    }

    /**
     * 道具消费
     * @param $time_type
     * @param $start_at
     * @param $end_at
     */
    public function giftFee($time_type, $start_at, $end_at)
    {
        if (!in_array($time_type, ArrayHelper::getColumn($this->_time_type, 'key'))) {
            return false;
        }

        $this->_start_at = $start_at;
        $this->_end_at = $end_at;
        $this->_type_name = ArrayHelper::map($this->_type, 'key', 'value')['gift_fee'];
        $this->_time_type_name = ArrayHelper::map($this->_time_type, 'key', 'value')[$time_type];

        $query = UserGiftLogs::find();
        $query->andWhere(['status' => 2]);
        $query->andWhere(['between', 'created', $start_at, $end_at]);
//        $sql = $query->createCommand()->rawSql;
        $count = $query->count('id');

        if (empty($count)) {
            $params = [
                'start_at' => strtotime($start_at),
                'end_at' => strtotime($end_at),
                'time_type' => $time_type,
                'type' => 'gift_fee',
                'created_by' => 1,
            ];

            return $this->saveModel($params);
        }

        //消费虚拟币总额
        $gift_value = $query->sum('gift_value');
        $gift_num = $query->sum('gift_num');
        $fee_1 = $gift_value * $gift_num;
        //总价值
        $fee_2 = $fee_1;

        $offset = ceil($count / $this->_pageSize);

        $data = [];
        for ($i = $this->_page; $i <= $offset; $i++) {
            $list = $query->select(['order_id', 'user_guid', 'gift_name', 'gift_num', 'gift_value', 'receiver', 'receiver_name', 'created', 'status'])
                ->orderBy('id')->offset($i * $this->_pageSize)->limit($this->_pageSize)->createCommand()
                ->queryAll();
//            ->rawSql;
            $userIds = array_unique(ArrayHelper::getColumn($list, 'user_guid'));
            $user = UserBaseInfor::find()->select(['user_guid', 'nick_name'])->andWhere(['user_guid' => $userIds])->all();
            $userName = ArrayHelper::map($user, 'user_guid', 'nick_name');

            foreach ($list as &$key) {
                $key['gift_value'] = intval($key['gift_value']);
                $key['gift_num'] = intval($key['gift_num']);
                $key['gift_sum'] = $key['gift_value'] * $key['gift_num'];

                $key['send_user_name'] = !empty($userName[$key['user_guid']]) ? $userName[$key['user_guid']] : '';
                switch ($key['status']) {
                    case 1:
                        $key['status'] = '等待支付';
                        break;
                    case 2:
                        $key['status'] = '支付成功';
                        break;
                    case 3:
                        $key['status'] = '支付失败';
                        break;
                    case 4:
                        $key['status'] = '补发成功';
                        break;
                    case 5:
                        $key['status'] = '补发失败';
                        break;
                }
            }
            unset($key);

            $data = array_merge($data, $list);
        }

        $table = [
            ['order_id', '订单号'],
            ['user_guid', '消费人ID'],
            ['send_user_name', '消费人昵称'],
            ['created', '消费时间'],
            ['gift_name', '道具名称'],
            ['gift_num', '道具数量'],
            ['receiver', '被赠送人ID'],
            ['receiver_name', '被赠送人昵称'],
            ['gift_sum', '消费金额/蛛丝'],
            ['status', '状态'],
        ];

        $filePath = $this->filePath($table, $data);

        if (empty($filePath)) {
            return false;
        }

        $params = [
            'fee_1' => $fee_1,
            'fee_2' => $fee_2,
            'start_at' => strtotime($start_at),
            'end_at' => strtotime($end_at),
            'time_type' => $time_type,
            'type' => 'gift_fee',
            'created_by' => 1,
            'file_url' => $filePath
        ];

        return $this->saveModel($params);

    }

    /**
     * 团队提现记录(无需发票)
     * @param $time_type
     * @param $start_at
     * @param $end_at
     */
    public function teamWithDrawFee($time_type, $start_at, $end_at)
    {
        if (!in_array($time_type, ArrayHelper::getColumn($this->_time_type, 'key'))) {
            return false;
        }

        $this->_start_at = $start_at;
        $this->_end_at = $end_at;
        $this->_type_name = ArrayHelper::map($this->_type, 'key', 'value')['team_with_draw_fee'];
        $this->_time_type_name = ArrayHelper::map($this->_time_type, 'key', 'value')[$time_type];

        $query = TeamAccountLog::find();
        $query->andWhere(['type' => 1]);
        $query->andWhere(['status' => 4]);
        $query->andWhere(['between', 'remit_at', strtotime($start_at), strtotime($end_at)]);
//        $sql = $query->createCommand()->rawSql;
        $count = $query->count('id');

        if (empty($count)) {
            $params = [
                'start_at' => strtotime($start_at),
                'end_at' => strtotime($end_at),
                'time_type' => $time_type,
                'type' => 'team_with_draw_fee',
                'created_by' => 1,
            ];

            return $this->saveModel($params);
        }

        //申请提现金额
        $newQuery = clone $query;
        $fee_1 = $newQuery->sum('amount');
        $fee_1 = $fee_1 * 100;
        //实际支付金额
        $newQuery = clone $query;
        $fee_2 = $newQuery->sum('receive_amount');
        $fee_2 = $fee_2 * 100;
        //产生税费
        $fee_3 = $fee_1 - $fee_2;

        $offset = ceil($count / $this->_pageSize);

        $data = [];
        for ($i = $this->_page; $i <= $offset; $i++) {
            $list = $query->select(['user_id as user_guid', 'amount', 'receive_amount', 'created_at'])
                ->orderBy('id')->offset($i * $this->_pageSize)->limit($this->_pageSize)->createCommand()
                ->queryAll();
//            ->rawSql;

            $userIds = array_unique(ArrayHelper::getColumn($list, 'user_guid'));
            $userRealInfo = UserRealInfo::find()->select(['user_guid', 'identity', 'real_name', 'phone'])->andWhere(['user_guid' => $userIds])->all();
            $identity = ArrayHelper::map($userRealInfo, 'user_guid', 'identity');
            $realName = ArrayHelper::map($userRealInfo, 'user_guid', 'real_name');
            $phone = ArrayHelper::map($userRealInfo, 'user_guid', 'phone');
            foreach ($list as &$key) {
                $key['created_at'] = date('Y-m-d H:i:s', $key['created_at']);
                $key['identity'] = !empty($identity[$key['user_guid']]) ? $identity[$key['user_guid']] : '';
                $key['real_name'] = !empty($realName[$key['user_guid']]) ? $realName[$key['user_guid']] : '';
                $key['phone'] = !empty($phone[$key['user_guid']]) ? $phone[$key['user_guid']] : '';
                $key['amount'] = floatval($key['amount']);
                $key['receive_amount'] = floatval($key['receive_amount']);
                $key['tax_1'] = floatval(round($key['amount'] * 0.2, 2));
                $key['tax_2'] = floatval(round($key['amount'] * 0.0672, 2));
                $key['tax_3'] = floatval(round($key['amount'] * 0.01, 2));
            }
            unset($key);

            $data = array_merge($data, $list);
        }

        $table = [
            ['real_name', '申请人姓名'],
            ['identity', '身份证号'],
            ['phone', '申请人电话'],
            ['created_at', '申请提现时间'],
            ['amount', '申请提现金额'],
            ['tax_1', '个税20%'],
            ['tax_2', '增值税6.72%'],
            ['tax_3', '通道费1%'],
            ['receive_amount', '实际提现'],
        ];


        $filePath = $this->filePath($table, $data);

        if (empty($filePath)) {
            return false;
        }

        $params = [
            'fee_1' => $fee_1,
            'fee_2' => $fee_2,
            'fee_3' => $fee_3,
            'start_at' => strtotime($start_at),
            'end_at' => strtotime($end_at),
            'time_type' => $time_type,
            'type' => 'team_with_draw_fee',
            'created_by' => 1,
            'file_url' => $filePath
        ];

        return $this->saveModel($params);
    }

    /**
     * 团队提现记录(需要发票)
     * @param $time_type
     * @param $start_at
     * @param $end_at
     */
    public function teamWithDrawInvoiceFee($time_type, $start_at, $end_at)
    {
        if (!in_array($time_type, ArrayHelper::getColumn($this->_time_type, 'key'))) {
            return false;
        }

        $this->_start_at = $start_at;
        $this->_end_at = $end_at;
        $this->_type_name = ArrayHelper::map($this->_type, 'key', 'value')['team_with_draw_invoice_fee'];
        $this->_time_type_name = ArrayHelper::map($this->_time_type, 'key', 'value')[$time_type];

        $query = TeamAccountLog::find();
        $query->andWhere(['type' => 2]);
        $query->andWhere(['status' => 4]);
        $query->andWhere(['between', 'remit_at', strtotime($start_at), strtotime($end_at)]);
//        $sql = $query->createCommand()->rawSql;
        $count = $query->count('id');

        if (empty($count)) {
            $params = [
                'start_at' => strtotime($start_at),
                'end_at' => strtotime($end_at),
                'time_type' => $time_type,
                'type' => 'team_with_draw_invoice_fee',
                'created_by' => 1,
            ];

            return $this->saveModel($params);
        }

        //申请打款金额
        $newQuery = clone $query;
        $fee_1 = $newQuery->sum('amount');
        $fee_1 = $fee_1 * 100;
        //实际打款金额
        $newQuery = clone $query;
        $fee_2 = $newQuery->sum('receive_amount');
        $fee_2 = $fee_2 * 100;

        $offset = ceil($count / $this->_pageSize);

        $data = [];
        for ($i = $this->_page; $i <= $offset; $i++) {
            $list = $query->select(['order_id', 'user_id as user_guid', 'amount', 'receive_amount', 'created_at', 'remit_at'])
                ->orderBy('id')->offset($i * $this->_pageSize)->limit($this->_pageSize)->createCommand()
                ->queryAll();
//            ->rawSql;

            $userIds = array_unique(ArrayHelper::getColumn($list, 'user_guid'));
            $userRealInfo = UserRealInfo::find()->select(['user_guid', 'real_name'])->andWhere(['user_guid' => $userIds])->all();
            $realName = ArrayHelper::map($userRealInfo, 'user_guid', 'real_name');


            foreach ($list as &$key) {
                $key['created_at'] = date('Y-m-d H:i:s', $key['created_at']);
                $key['remit_at'] = date('Y-m-d H:i:s', $key['remit_at']);
                $key['amount'] = floatval($key['amount']);
                $key['receive_amount'] = floatval($key['receive_amount']);
                $key['main_company_name'] = '';
                $key['real_name'] = !empty($realName[$key['user_guid']]) ? $realName[$key['user_guid']] : '';
            }
            unset($key);

            $data = array_merge($data, $list);
        }

        $table = [
            ['real_name', '申请人姓名'],
            ['order_id', '申请单ID'],
            ['main_company_name', '申请人所属公司'],
            ['created_at', '申请提现时间'],
            ['amount', '申请提现金额'],
            ['remit_at', '实际打款时间'],
            ['receive_amount', '实际打款金额'],
        ];


        $filePath = $this->filePath($table, $data);

        if (empty($filePath)) {
            return false;
        }

        $params = [
            'fee_1' => $fee_1,
            'fee_2' => $fee_2,
            'start_at' => strtotime($start_at),
            'end_at' => strtotime($end_at),
            'time_type' => $time_type,
            'type' => 'team_with_draw_invoice_fee',
            'created_by' => 1,
            'file_url' => $filePath
        ];

        return $this->saveModel($params);
    }


    public function filePath($table, $data)
    {
        if (empty($table) || empty($data)) {
            return false;
        }


        $serial = range('A', 'Z');
        $serialTotal = count($table);

        $objectPHPExcel = new PHPExcel();
        $objectPHPExcel->setActiveSheetIndex(0);

        $objectPHPExcel->getActiveSheet()->mergeCells("{$serial[0]}1:{$serial[$serialTotal - 1]}1");
        $objectPHPExcel->getActiveSheet()->setCellValue("{$serial[0]}1", $this->_type_name . '(' . $this->_time_type_name . ')');
        $objectPHPExcel->getActiveSheet()->getStyle("{$serial[0]}1")->getFont()->setSize(24);
        $objectPHPExcel->getActiveSheet()->getStyle("{$serial[0]}1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        for ($i = 0; $i < $serialTotal; $i++) {
            $objectPHPExcel->getActiveSheet()->setCellValue($serial[$i] . '2', $table[$i][1]);
        }

        $row = 3;
        foreach ($data as $key => $value) {
            for ($i = 0; $i < $serialTotal; $i++) {
                if (is_string($value[$table[$i][0]])) {
                    $objectPHPExcel->getActiveSheet()->setCellValueExplicit($serial[$i] . $row, $value[$table[$i][0]]);
                } else {
                    $objectPHPExcel->getActiveSheet()->setCellValue($serial[$i] . $row, $value[$table[$i][0]]);
                }
            }
            $row++;
        }

        $objWriter = PHPExcel_IOFactory::createWriter($objectPHPExcel, 'Excel2007');
        $_type_name = ArrayHelper::map($this->_type, 'value', 'key')[$this->_type_name];
        $_time_type_name = ArrayHelper::map($this->_time_type, 'value', 'key')[$this->_time_type_name];
        $dir = $_type_name . '-' . $_time_type_name . '-' . date('Ymd', strtotime($this->_start_at)) . '-' . date('Ymd', strtotime($this->_end_at)) . '.xlsx';
        $path = self::getUploadPath('') . DIRECTORY_SEPARATOR . $dir;

        try {
            $objWriter->save($path);
            return self::getLink($path);
        } catch (\Exception $e) {
            return false;
        }
    }

    protected static function getUploadPath($dir)
    {
        $uploadPath = Yii::getAlias('@public') . DIRECTORY_SEPARATOR . 'uploads' . DIRECTORY_SEPARATOR . 'data-report' . ($dir ? DIRECTORY_SEPARATOR . $dir : '');
        if (!FileHelper::createDirectory($uploadPath, 0777)) {
            throw new Exception('Cannot create "' . $uploadPath . '". Please check write permissions.');
        }
        return $uploadPath;
    }

    protected static function getLink($fileName)
    {
        return str_replace('\\', '/', str_replace(Yii::getAlias('@public'), '', $fileName));
    }

    protected function findModel($time_type, $type, $start_at, $end_at)
    {
        if (($model = DataReportFeeLog::find()->where([
                'start_at' => $start_at,
                'end_at' => $end_at,
                'time_type' => $time_type,
                'type' => $type
            ])->one()) !== null) {
            return $model;
        } else {
            return new DataReportFeeLog();
        }
    }

    protected function saveModel($params)
    {
        $model = $this->findModel($params['time_type'], $params['type'], $params['start_at'], $params['end_at']);

        $model->fee_1 = !empty($params['fee_1']) ? $params['fee_1'] : 0;
        $model->fee_2 = !empty($params['fee_2']) ? $params['fee_2'] : 0;
        $model->fee_3 = !empty($params['fee_3']) ? $params['fee_3'] : 0;
        $model->start_at = $params['start_at'];
        $model->end_at = $params['end_at'];
        $model->time_type = $params['time_type'];
        $model->type = $params['type'];
        $model->created_by = 1;
        $model->file_url = !empty($params['file_url']) ? $params['file_url'] : '';

        return $model->save();
    }

    public function getCreatedBy()
    {
        return $this->hasOne(Admin::class, ['id' => 'created_by']);
    }

    public function getUpdatedBy()
    {
        return $this->hasOne(Admin::class, ['id' => 'updated_by']);
    }
}
